﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DTO;
using AutoMapper;

namespace DAO
{
   public class DocGiaRepository
    {
       public static DOC_GIA  CreateDocGiaEFFromDocGiaDTO(DocGiaDTO docGiaDTO)
       {
           
            DOC_GIA docGiaEF = new DOC_GIA();
           //Mapper.CreateMap<DocGiaDTO, DOC_GIA>();
           //docGiaEF = Mapper.Map<DocGiaDTO, DOC_GIA>(docGiaDTO);
            docGiaEF.MaDocGia = docGiaDTO.MaDocGia;
            docGiaEF.MaTheDocGia = docGiaDTO.MaTheDocGia;
           docGiaEF.HoTen = docGiaDTO.HoTen;
           docGiaEF.NgaySinh = docGiaDTO.NgaySinh;
           docGiaEF.DiaChi = docGiaDTO.DiaChi;
           docGiaEF.Email = docGiaDTO.Email;
           docGiaEF.MaLoaiDocGia = docGiaDTO.MaLoaiDocGia;
           docGiaEF.NgayLapThe = docGiaDTO.NgayLapThe;
           docGiaEF.NguoiLap = docGiaDTO.NguoiLap;
           docGiaEF.ThoiHanThe = docGiaDTO.ThoiHanThe;
           docGiaEF.SoSachDangMuon = docGiaDTO.SoSachDangMuon;
           docGiaEF.TienNo = docGiaDTO.TienNo;
           docGiaEF.DaXoa = docGiaDTO.DaXoa;
           return docGiaEF;
       }

       public static DocGiaDTO CreateDocGiaDTOFromDocGiaEF(DOC_GIA docGiaEF)
       {
           DocGiaDTO docGiaDTO = new DocGiaDTO();
           Mapper.CreateMap<DOC_GIA, DocGiaDTO>();
           docGiaDTO= Mapper.Map<DOC_GIA, DocGiaDTO>(docGiaEF);
           return docGiaDTO;
       }

       public int  LayMaDocGiaMax()
       {
           int maDG=0;
           try
           {
               QLTVEntities context = new QLTVEntities();
                maDG = context.DOC_GIAList.Max(s => s.MaDocGia);
           }
           catch
           { 
           }
           return maDG;           
       }

       public string PhatSinhMaTheDocGia(string maTheDG)
       {
           if (maTheDG.Length == 1)
               maTheDG = "00000"+maTheDG;
           if(maTheDG.Length == 2)
               maTheDG = "0000" + maTheDG;
           if(maTheDG.Length==3)
               maTheDG = "000" + maTheDG;
           if(maTheDG.Length == 4)
               maTheDG = "00" + maTheDG;
           if(maTheDG.Length == 5)
               maTheDG = "0" + maTheDG;
           return maTheDG;
       }
       public void ThemDocGia(DocGiaDTO docGiaDTO)
       {
           try
           {
               QLTVEntities context = ContextFactory.GetInstance();
               DOC_GIA docGiaEF = new DOC_GIA();               
               docGiaEF = CreateDocGiaEFFromDocGiaDTO(docGiaDTO);               

               context.AddToDOC_GIAList(docGiaEF);
               context.SaveChanges();
           }
           catch (System.Exception)
           { 

           }
       }

       public void CapNhatDocGia(DocGiaDTO docGiaDTO)
       {
           try
           {
               QLTVEntities context = ContextFactory.GetInstance();
               DOC_GIA docGiaEF;
               var query = from docGia in context.DOC_GIAList
                           where docGia.MaDocGia == docGiaDTO.MaDocGia
                           select docGia;
               docGiaEF = query.First();
               docGiaEF = CreateDocGiaEFFromDocGiaDTO(docGiaDTO);               
               

               context.DOC_GIAList.ApplyCurrentValues(docGiaEF);
               context.SaveChanges();                   
           }
           catch (System.Exception)
           {

           }
       }

       public void XoaDocGia(int maDocGia, DocGiaDTO docGiaDTO)
       {
           try
           {
               QLTVEntities context = ContextFactory.GetInstance();
               var query = from docGia in context.DOC_GIAList
                           where docGia.MaDocGia == maDocGia
                           select docGia;
               DOC_GIA docGiaTemp = query.First();
               if (docGiaTemp != null)
               {
                   docGiaTemp = CreateDocGiaEFFromDocGiaDTO(docGiaDTO);
                   context.DOC_GIAList.ApplyCurrentValues(docGiaTemp);                   
                   context.SaveChanges();
               }               
           }
           catch (System.Exception)
           {

           }
       }
       public DocGiaDTO LayThongTinDocGia(int maDocGia)
       {
           DocGiaDTO docGiaDTO = new DocGiaDTO();
           try
           {
               QLTVEntities context = new QLTVEntities();
               var query = from docGia in context.DOC_GIAList
                           where docGia.MaDocGia == maDocGia
                           select docGia;
               DOC_GIA docGiaTemp = query.First();
               if (docGiaTemp != null)
                   docGiaDTO = CreateDocGiaDTOFromDocGiaEF(docGiaTemp);
           }
           catch (System.Exception)
           {

           }
           return docGiaDTO;
       }

       public DataTable TraCuuDocGia(string hoTen, int maLoaiDocGia)
       {
           DataTable dbTraCuu = new DataTable();
           QLTVEntities context = new QLTVEntities();
           var query = from docGia in context.DOC_GIAList
                       from loaiDG in context.LOAI_DOCGIAList
                       where docGia.MaLoaiDocGia == loaiDG.MaLoaiDocGia
                       select new 
                       {
                           docGia.MaDocGia,
                           docGia.MaTheDocGia,
                           docGia.HoTen, 
                           docGia.DiaChi,
                           docGia.Email,
                           docGia.MaLoaiDocGia,
                           loaiDG.TenLoaiDocGia,
                           docGia.TienNo
                       };
           if (hoTen != "")
               query = query.Where(docGia => docGia.HoTen.Contains(hoTen));
           if (maLoaiDocGia != 0)
               query = query.Where(docGia=>docGia.MaLoaiDocGia==maLoaiDocGia);

           dbTraCuu.Columns.Add("MaDocGia");
           dbTraCuu.Columns.Add("MaTheDocGia");
           dbTraCuu.Columns.Add("HoTen");
           dbTraCuu.Columns.Add("DiaChi");
           dbTraCuu.Columns.Add("Email");
           dbTraCuu.Columns.Add("TenLoaiDocGia");
           dbTraCuu.Columns.Add("TienNo");

           foreach (var docGia in query)
           {
               DataRow dong = dbTraCuu.NewRow();
               dong["MaDocGia"] = docGia.MaDocGia;
               dong["MaTheDocGia"] = docGia.MaTheDocGia;
               dong["HoTen"] = docGia.HoTen;
               dong["DiaChi"] = docGia.DiaChi;
               dong["Email"] = docGia.Email;
               dong["TenLoaiDocGia"] = docGia.TenLoaiDocGia;
               dong["TienNo"] = docGia.TienNo;
               dbTraCuu.Rows.Add(dong);
           }

           dbTraCuu.Columns.Add("STT");
           DataProvider.ThemSoThuTu(dbTraCuu);
           return dbTraCuu;
       }

       public DataTable ThongKeDocGiaNoTienPhat()
       {
           DataTable bangDocGia = new DataTable();
           QLTVEntities context = new QLTVEntities();
           var query = from docGia in context.DOC_GIAList
                       where docGia.TienNo > 0
                       select docGia;
           bangDocGia.Columns.Add("MaTheDocGia");
           bangDocGia.Columns.Add("HoTen");
           bangDocGia.Columns.Add("NgaySinh");
           bangDocGia.Columns.Add("DiaChi");
           bangDocGia.Columns.Add("TienNo");
           DataRow dong = bangDocGia.NewRow();
           foreach (var docGia in query)
           {
               dong["MaTheDocGia"] = docGia.MaTheDocGia;
               dong["HoTen"] = docGia.HoTen;
               dong["NgaySinh"] = docGia.NgaySinh;
               dong["DiaChi"] = docGia.DiaChi;
               dong["TienNo"] = docGia.TienNo;
               bangDocGia.Rows.Add(dong);
           }
           return bangDocGia;
       }

       public DataTable TimDocGiaCoSachMuonQuaHan()
       {
           DataTable dbTraCuu = new DataTable();
           QLTVEntities context = new QLTVEntities();

           return dbTraCuu;
       }
    }
}
